package com.dp.service.impl3;

import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.util.DataMap;
import com.dp.vo.TServiceInfo;
import com.dp.vo.TUser;

@Service
@Transactional
public class ServiceServiceImpl3 extends BaseImpl {
	
	public  TServiceInfo getServiceByCode(String servicecode)
	{
		TServiceInfo s = null;
		this.sql = "SELECT n FROM TServiceInfo n where n.serviceCode=:serviceCode";
		try {
			Query query = em.createQuery(sql);
			query.setParameter("serviceCode", servicecode);
			s = (TServiceInfo) query.getSingleResult();
		} catch (Exception e) {
			log.error(" getServiceByCode fail !!!" +e.getMessage());
			e.printStackTrace();
		}
		return s;
	}

	public Integer findAllServiceCount(DataMap hashMap){
		TServiceInfo serviceone = (TServiceInfo) hashMap.get("service");
		Object s_role_id = hashMap.get("role_id");
		Object s_emp_id = hashMap.get("employee_id");
		Object pageUserId = hashMap.get("pageUserId");
		Object empids = hashMap.get("empids");
		Integer iCount = 0; 
		try {
		//	this.init();
			if(s_role_id != null && !"".equals(s_role_id))
			{
				sql = "SELECT count(ts.serviceid) FROM t_service_info ts,t_user_prices tup where tup.employee_id='"+s_emp_id+"' and ts.service_code=tup.service_code";
				if(serviceone != null)
				{
					if(serviceone.getServiceCode()!=null && serviceone.getServiceCode().toString().trim().length() > 0)
					{
						sql = sql + " and ts.service_code like '%"+serviceone.getServiceCode()+"%' ";
					}
					if(serviceone.getPriceType()!=null && serviceone.getPriceType().toString().trim().length() > 0)
					{
						sql = sql + " and ts.price_type = '" + serviceone.getPriceType() + "' ";
					}
					if(serviceone.getServicetype()!=null && serviceone.getServicetype().toString().trim().length() > 0)
					{
						sql = sql + " and ts.servicetype = '" + serviceone.getServicetype() + "' ";
					}
				}
			}
			else
			{			
				sql = "select count(a.a) from (SELECT distinct ts.serviceid a FROM t_service_info ts left join " +
				"(select tup.service_code,tu.user_id from t_user tu left join t_user_prices tup on tu.employee_id=tup.employee_id) tups "+
				"on ts.service_code=tups.service_code where 1=1";
				
				if(empids != null)
				{
					sql = "select count(a.a) from (SELECT distinct ts.serviceid a FROM t_service_info ts , " +
					"(select tup.service_code,tu.user_id from t_user tu left join t_user_prices tup on tu.employee_id=tup.employee_id where " +
					"tup.employee_id in ("+empids+")) tups where ts.service_code=tups.service_code ";
				}
				
				if(serviceone != null)
				{
					if(serviceone.getServiceCode()!=null && serviceone.getServiceCode().toString().trim().length() > 0)
					{
						sql = sql + " and ts.service_code like '%"+serviceone.getServiceCode()+"%' ";
					}
					if(serviceone.getPriceType()!=null && serviceone.getPriceType().toString().trim().length() > 0)
					{
						sql = sql + " and ts.price_type = '" + serviceone.getPriceType() + "' ";
					}
					if(serviceone.getServicetype()!=null && serviceone.getServicetype().toString().trim().length() > 0)
					{
						sql = sql + " and ts.servicetype = '" + serviceone.getServicetype() + "' ";
					}
					if(pageUserId!=null && pageUserId.toString().trim().length() > 0)
					{
						sql = sql + " and tups.user_id like '%"+pageUserId+"%' ";
					}
				}
				sql = sql + " ) a";
			}

			logger.info("findAllServiceCount excute sql: " + sql);
			
				iCount=this.getOneInt();
		} catch (Exception e) {
			logger.error("findAllServiceCount fail !!!" + e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	public List<TServiceInfo> findAllService(DataMap hashMap, Integer firstrow,Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		TServiceInfo serviceone = (TServiceInfo) hashMap.get("service");
		Object s_role_id = hashMap.get("role_id");
		Object s_emp_id = hashMap.get("employee_id");
		Object pageUserId = hashMap.get("pageUserId");
		Object empids = hashMap.get("empids");
		List<TServiceInfo> serviceList = null; 
		try {
		
			if(s_role_id != null && !"".equals(s_role_id))
			{
				sql = "SELECT ts.serviceid,ts.servicename,ts.service_code,ts.price,ts.price_type,ts.servicetype,ts.access_no,ts.port_type,ts.content,ts.content_type,ts.processClass,ts.carrier,ts.spnumber,ts.order_success_msg,ts.ctrmand_cmd,ts.ctrmand_no,ts.starttime" +
						" FROM t_service_info ts,t_user_prices tup where tup.employee_id='"+s_emp_id+"' and ts.service_code=tup.service_code";
				if(serviceone != null)
				{
					if(serviceone.getServiceCode()!=null && serviceone.getServiceCode().toString().trim().length() > 0)
					{
						sql = sql + " and ts.service_code like '%"+serviceone.getServiceCode()+"%' ";
					}
					if(serviceone.getPriceType()!=null && serviceone.getPriceType().toString().trim().length() > 0)
					{
						sql = sql + " and ts.price_type = '" + serviceone.getPriceType() + "' ";
					}
					if(serviceone.getServicetype()!=null && serviceone.getServicetype().toString().trim().length() > 0)
					{
						sql = sql + " and ts.servicetype = '" + serviceone.getServicetype() + "' ";
					}
				}
			}
			else
			{			
				sql = "SELECT distinct ts.serviceid,ts.servicename,ts.service_code,ts.price,ts.price_type,ts.servicetype,ts.access_no,ts.port_type,ts.content,ts.content_type,ts.processClass,ts.carrier,ts.spnumber,ts.order_success_msg,ts.ctrmand_cmd,ts.ctrmand_no,ts.starttime" +
						" FROM t_service_info ts left join " +
				"(select tup.service_code,tu.user_id from t_user tu left join t_user_prices tup on tu.employee_id=tup.employee_id) tups "+
				"on ts.service_code=tups.service_code where 1=1";
				
				if(empids != null)
				{
					sql = "SELECT distinct ts.serviceid,ts.servicename,ts.service_code,ts.price,ts.price_type,ts.servicetype,ts.access_no,ts.port_type,ts.content,ts.content_type,ts.processClass,ts.carrier,ts.spnumber,ts.order_success_msg,ts.ctrmand_cmd,ts.ctrmand_no,ts.starttime" +
							" FROM t_service_info ts , " +
					"(select tup.service_code,tu.user_id from t_user tu left join t_user_prices tup on tu.employee_id=tup.employee_id where " +
					"tup.employee_id in ("+empids+")) tups where ts.service_code=tups.service_code ";
				}
				
				if(serviceone != null)
				{
					if(serviceone.getServiceCode()!=null && serviceone.getServiceCode().toString().trim().length() > 0)
					{
						sql = sql + " and ts.service_code like '%"+serviceone.getServiceCode()+"%' ";
					}
					if(serviceone.getPriceType()!=null && serviceone.getPriceType().toString().trim().length() > 0)
					{
						sql = sql + " and ts.price_type = '" + serviceone.getPriceType() + "' ";
					}
					if(serviceone.getServicetype()!=null && serviceone.getServicetype().toString().trim().length() > 0)
					{
						sql = sql + " and ts.servicetype = '" + serviceone.getServicetype() + "' ";
					}
				}
				
				if(pageUserId!=null && pageUserId.toString().trim().length() > 0)
				{
					sql = sql + " and tups.user_id like '%"+pageUserId+"%' ";
				}
			}
			
			if(jpage!=null){
				if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
					sql += " order by ts."+jpage.getSidx()+" "+jpage.getSord();
				}
			}
			if(firstrow!=null&&maxrow!=null){
				sql += " limit "+firstrow+","+maxrow;
			}
			logger.info("findAllService excute sql: " + sql);
			ArrayList result = this.queryALLResultSet(sql);
			serviceList=new ArrayList<TServiceInfo>(result.size());
			TServiceInfo one = null;
			SimpleDateFormat fmt_temp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			if(result.size()>0)
			{
				for(int i=0;i<result.size();i++)
				{
					HashMap tmp = (HashMap)result.get(i);
					one=new TServiceInfo();
					one.setServiceid(Long.parseLong((String)tmp.get("serviceid")));
					one.setServicename((String)tmp.get("servicename"));
					one.setServiceCode((String)tmp.get("service_code"));
					one.setPrice(BigInteger.valueOf(Long.parseLong((String)tmp.get("price"))));
					one.setPriceType((String)tmp.get("price_type"));
					one.setServicetype((String)tmp.get("servicetype"));
					one.setAccessNo((String)tmp.get("access_no"));
					one.setPortType((String)tmp.get("port_type"));
					one.setContent((String)tmp.get("content"));
					one.setContentType((String)tmp.get("content_type"));
					one.setProcessClass((String)tmp.get("processclass"));;
					one.setCarrier((String)tmp.get("carrier"));
					one.setSpnumber((String)tmp.get("spnumber"));
					one.setOrderSuccessMsg((String)tmp.get("order_success_msg"));
					one.setCtrmand_cmd((String)tmp.get("ctrmand_cmd"));
					one.setCtrmand_no((String)tmp.get("ctrmand_no"));
					one.setStarttime(fmt_temp.parse((String)tmp.get("starttime")));
					serviceList.add(one);
				}
			}
		
		} catch (Exception e) {
			logger.error("findAllService fail !!!" + e.getMessage());
			e.printStackTrace();
		} 
		return serviceList;
	}
	
	public List<TUser> getAllUser()
	{
		this.sql = "SELECT n FROM TUser n";
		List<TUser> result = null;
		try {
			Query query = em.createQuery(sql);
			result = query.getResultList();
		} catch (Exception e) {
			log.error(" getAllUser fail !!!" +e.getMessage());
			e.printStackTrace();
		}
		return result;
	}
	
	public List<TUser> getSelectUser(String servicecode)
	{
		this.sql = "SELECT employee_id FROM t_user_prices where service_code='"+servicecode+"'";
		List<TUser> userList = null;
		try {
			ArrayList result = this.queryALLResultSet(sql);
			userList=new ArrayList<TUser>(result.size());
			TUser one = null;
			if(result.size()>0)
			{
				for(int i=0;i<result.size();i++)
				{
					HashMap tmp = (HashMap)result.get(i);
					one=new TUser();
					one.setEmployeeId((String)tmp.get("employee_id"));
					userList.add(one);
				}
			}
		
		} catch (Exception e) {
			log.error(" getSelectUser fail !!!" +e.getMessage());
			e.printStackTrace();
		}
		return userList;
	}
	
	public boolean checkServiceCode(String servicecode)
	{
		boolean t = false;
		try {
			
			sql=("select count(service_code) as totalrow from t_service_info where service_code='"+servicecode+"'");
			t=this.getOneInt()>0;
		}
		catch (Exception e) {
			logger.error("checkServiceCode fail !!!" + e.getMessage());
			e.printStackTrace();
		}
	
		return t;
	}
	
	public boolean insertService(TServiceInfo one,String selUserStr,String srvStarttime)
	{
		ArrayList<String> sqlList = new ArrayList<String>();
		boolean r = false;
		String servicecode = ObjectToStringDB(one.getServiceCode());
		String servicename = ObjectToStringDB(one.getServicename());
		BigInteger price = one.getPrice();
		String price_type = ObjectToStringDB(one.getPriceType());
		String servicetype = ObjectToStringDB(one.getServicetype());
		String access_no = ObjectToStringDB(one.getAccessNo());
		String content = ObjectToStringDB(one.getContent());
		String processClass = ObjectToStringDB(one.getProcessClass());
		String order_success_msg = ObjectToStringDB(one.getOrderSuccessMsg());
		String carrier = ObjectToStringDB(one.getCarrier());
		String spnumber = ObjectToStringDB(one.getSpnumber());
		String ctrmand_cmd = ObjectToStringDB(one.getCtrmand_cmd());
		String ctrmand_no = ObjectToStringDB(one.getCtrmand_no());
		if(srvStarttime != null && srvStarttime.length()>0)
			srvStarttime = "'"+srvStarttime+"'";
		else
			srvStarttime = "now()";
		sql = "insert into t_service_info (servicename, service_code, price, price_type, servicetype, access_no, content, processclass ," +
				" carrier , spnumber,order_success_msg, ctrmand_no,ctrmand_cmd,starttime) " +
		"values ("+servicename+","+servicecode+","+price+","+price_type+","+servicetype+","+access_no+","+content+","+processClass+","
		+carrier+","+spnumber+","+order_success_msg+","+ctrmand_no+","+ctrmand_cmd+","+srvStarttime+")";
		sqlList.add(sql);
		if(selUserStr!=null && selUserStr.length() > 0)
		{
			String[] user = selUserStr.split(",");
			for(String empid: user){
				sql = "insert into t_user_prices values (" + servicecode+","+empid+")";
				sqlList.add(sql);
			}
		}
		try {
			r = this.execTransaction(sqlList);
		} catch (Exception e) {
			logger.error("insertService error!"+e.getMessage());
			e.printStackTrace();
		}
		return r;
	}
	
	public boolean updateNews(TServiceInfo one,String roleid)
	{
		boolean r = false;
		String servicecode = ObjectToStringDB(one.getServiceCode());
		String servicename = ObjectToStringDB(one.getServicename());
		BigInteger price = one.getPrice();
		String access_no = ObjectToStringDB(one.getAccessNo());
		String content = ObjectToStringDB(one.getContent());
		String order_success_msg = ObjectToStringDB(one.getOrderSuccessMsg());
		String ctrmand_cmd = ObjectToStringDB(one.getCtrmand_cmd());
		String ctrmand_no = ObjectToStringDB(one.getCtrmand_no());
		
		if(roleid != null && !roleid.equals(""))
			sql = "update t_service_info set order_success_msg="+order_success_msg+" where service_code ="+servicecode;
		else
			sql = "update t_service_info set servicename="+servicename+",price="+price+",access_no="+access_no+",content="+content+"," + 
			"order_success_msg="+order_success_msg+",ctrmand_cmd="+ctrmand_cmd+",ctrmand_no="+ctrmand_no+" where service_code ="+servicecode;

		try {
			r = this.execUpdate(sql);
		} catch (Exception e) {
			logger.error("updateNews error!"+e.getMessage());
			e.printStackTrace();
		}
		return r;
	}
	
	public boolean assignUser(TServiceInfo one,String selUserStr)
	{
		ArrayList<String> sqlList = new ArrayList<String>();
		boolean r = false;
		String servicecode = ObjectToStringDB(one.getServiceCode());
		
		if(selUserStr!=null && selUserStr.length() > 0)
		{
			sql = "delete from t_user_prices where service_code="+servicecode;
			sqlList.add(sql);
			String[] user = selUserStr.split(",");
			for(String empid: user){
				sql = "insert into t_user_prices values (" + servicecode+","+empid+")";
				sqlList.add(sql);
			}
		}
		try {
			r = this.execTransaction(sqlList);
		} catch (Exception e) {
			logger.error("assignUser error!"+e.getMessage());
			e.printStackTrace();
		}
		return r;
	}
	
	public List<TUser> findAllChildUser(String spcodeS) {
		List<TUser> childUsers = null;
		sql="select tu.user_id,tu.employee_id from t_employee te join t_user tu on te.employee_id=tu.employee_id where te.sp_code in ("+spcodeS+")";
		logger.info("findAllChildUser excute sql: " + sql);
		try {
			ArrayList result = this.queryALLResultSet(sql);
			childUsers = new ArrayList<TUser>(result.size());
			TUser one = null;
			if(result.size()>0)
			{
				for(int i=0;i<result.size();i++)
				{
					HashMap tmp = (HashMap)result.get(i);
					one=new TUser();
					one.setUserId((String)tmp.get("user_id"));
					one.setEmployeeId((String)tmp.get("employee_id"));
					childUsers.add(one);
				}
			}
			
		} catch (Exception e) {
			logger.error("findAllChildUser fail !!!" + e.getMessage());
			e.printStackTrace();
		}
		return childUsers;
	}
	
	public String findAllEmpid(String spcodeS) {
		String userids = "";
		try {
	       
			sql="select tu.employee_id from t_employee te join t_user tu on te.employee_id=tu.employee_id where te.sp_code in ("+spcodeS+")";
			
			String empid = null;
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
	    	for(Object[]o:tempp)
	    	{
				empid=this.getString(o[0]);
				userids+="'"+empid+"',";
			}
			if(!"".equals(userids)){
				userids=userids.substring(0, userids.length()-1);
        	}
		 }catch (Exception e) {
			logger.error("findAllEmpid fail !!!" + e.getMessage());
			e.printStackTrace();
		}
		return userids;
	}
	
//	private String ObjectToStringDB(Object obj) {
//        try {
//            String str = obj.toString().trim();
//            if (str.equals("") || str.equals("null")) {
//                return "null";
//            }
//            return "'" + str + "'";
//        } catch (Exception e) {
//            return "null";
//        }
//    }
}
